--编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。
create view V_AIfo(卡号,身份证,姓名,余额)
as
select b.CardNo,a.AccountCode,a.RealName,b.CardMoney from AccountInfo a
inner join BankCard b on a.AccountId = b.AccountId
go

select * from V_AIfo

create table [mod] (
	[year] int,
	[month] int,
	amount float
)
insert into [mod] values
(1991,1,1.1),
(1991,2,1.2),
(1991,3,1.3),
(1991,4,1.4),
(1992,1,2.1),
(1992,2,2.2),
(1992,3,2.3),
(1992,4,2.4)
go

--列转行
select [year],
max(case when [month] = 1 then amount else 0 end) m1,
max(case when [month] = 2 then amount else 0 end) m2,
max(case when [month] = 3 then amount else 0 end) m3,
max(case when [month] = 4 then amount else 0 end) m4
 from [mod] 
group by [year]


select * from [mod] 
pivot
(
sum(amount)
for [month] in ([1],[2],[3],[4])
) as amount


CREATE TABLE Sales(
[Year] INT,
A1 INT,
A2 INT,
A3 INT,
A4 INT);
GO
INSERT INTO Sales VALUES 
(2017,12,123,324,123),
(2018,13,455,324,878),
(2019,29,786,473,633);
--行转列
select [Year],'A1' as peopel,A1 as Amonut from Sales union all
select [Year],'A2' as peopel,A2 as Amonut from Sales union all
select [Year],'A3' as peopel,A3 as Amonut from Sales union all
select [Year],'A4' as peopel,A4 as Amonut from Sales

select * from Sales
unpivot (Amount for people in (A1,A2,A3,A4)) as a